package com.cpi.cornpp.purchaseinstructiondetail.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.instructionsforecasts.exception.InstructionsForecastsException;
import com.cpi.cornpp.instructionspurchases.exception.InstructionPurchaseException;
import com.cpi.cornpp.product.dao.ProductDAO;
import com.cpi.cornpp.product.exception.ProductException;
import com.cpi.cornpp.purchaseinstructiondetail.dto.InstructionPurchaseDetailDTO;
import com.cpi.cornpp.purchaseinstructiondetail.exception.InstructionPurchaseDetailException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class InstructionPurchaseDetailDAO extends DBDAO {
	
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	private static final String SQL_INSERT_DETAIL = "INSERT INTO purchases_instructions_details ( purchase_instruction_id, row, customer_id, item_quantity, corn_quantity, future_type_id, instruction_strategy_id, instruction_type_id, status ) select ?,isnull((select max(row)+1 from purchases_instructions_details where purchase_instruction_id = ?),1),?,?,?,?,?,?,?";//$NON-NLS-1$
	private static final String SQL_INSERT_PRODUCT_DETAIL = "insert into purchases_instructions_items_details (purchase_instruction_detail_id, item_id) values (?, ?)";//$NON-NLS-1$
	private static final String SELECT_PURCHASE_INSTRUCTION_DETAILS = 
		"SELECT " +
		"pid.purchase_instruction_detail_id, " +
		"pid.purchase_instruction_id, " +
		"pid.row, " +
		"pid.customer_id, " +
		"vc.description, " +
		"pid.item_quantity, " +
		"pid.corn_quantity, " +
		"pid.future_type_id, " +
		"pid.instruction_strategy_id, " +
		"pid.instruction_type_id, " +
		"(select sum( quantity )  from purchases_instructions_futures where purchase_instruction_detail_id = pid.purchase_instruction_detail_id), " + // -- Futuros
		"(select sum( quantity )  from purchases_instructions_futures where purchase_instruction_detail_id = pid.purchase_instruction_detail_id)/ (select qty from units where unit_id = 'CON')," +//-- Toneladas
		"pid.status " +
		" FROM purchases_instructions_details pid INNER JOIN vw_customers vc on pid.customer_id = vc.customer_id ";//$NON-NLS-1$
	
	/** Query para seleccionar un solo purchase instruction detail*/
	private static final String SELECT_PURCHASE_INSTRUCTION_DETAIL = 
		"SELECT " +
		"pid.purchase_instruction_detail_id, " +
		"pid.purchase_instruction_id, " +
		"pid.row, " +
		"pid.customer_id, " +
		"vc.description, " +
		"pid.item_quantity, " +
		"pid.corn_quantity, " +
		"pid.future_type_id, " +
		"pid.instruction_strategy_id, " +
		"pid.instruction_type_id, " +
		"(select sum( quantity )  from purchases_instructions_futures where purchase_instruction_detail_id = pid.purchase_instruction_detail_id), " + // -- Futuros
		"(select sum( quantity )  from purchases_instructions_futures where purchase_instruction_detail_id = pid.purchase_instruction_detail_id)/ (select qty from units where unit_id = 'CON')," +//-- Toneladas
		"pid.status " +
		" FROM purchases_instructions_details pid INNER JOIN vw_customers vc on pid.customer_id = vc.customer_id " +
		" WHERE purchase_instruction_detail_id = ?";//$NON-NLS-1$
	
	/** Query para actualizar un purchase instruction detail*/
	private static final String SQL_UPDATE_DETAIL = 
			" UPDATE purchases_instructions_details SET " +
			" future_type_id = ?, " +
			" instruction_strategy_id = ?, " +
			" instruction_type_id= ?, " +
			" item_quantity=?,"+
			" corn_quantity=?,"+
			" status= ? " +
			" WHERE purchase_instruction_detail_id = ?";
	
	
	private static final String ID_FILTER = " WHERE purchase_instruction_id = ? ";	
	
	private static final String ORDER_BY_ROW = " ORDER BY ROW ";
	private static final String SQL_CHANGE_STATUS_DETAIL = " UPDATE purchases_instructions_details SET status= ? where purchase_instruction_id = ? ";
	private static final String SQL_CHANGE_STATUS_DETAIL_BY_ID = " UPDATE purchases_instructions_details SET status= ? where purchase_instruction_detail_id = ? ";
	
	/**
	 * Devuelve la lista de purchase instruction details 
	 * @author Edgar Joao
	 * @return
	 * @throws InstructionsForecastsException
	 */
	public List getPurchaseInstructionDetails( int purchaseInstructionId) throws InstructionsForecastsException{
		List listOfPurchaseInstructionDetails = null;
		PreparedStatement preparedStatement = null;
		InstructionPurchaseDetailDTO dto = null;
		StringBuffer sql = null;
		ResultSet rs = null;
		ProductDAO productDAO = null;
		try{
			
			sql = new StringBuffer(0);
			sql.append(SELECT_PURCHASE_INSTRUCTION_DETAILS);
			if(purchaseInstructionId != -1){
				sql.append(ID_FILTER);
			}
			sql.append(ORDER_BY_ROW);
			listOfPurchaseInstructionDetails = new ArrayList(0);			
			preparedStatement = prepareSQL(sql.toString(), DATASOURCE);			
			if(purchaseInstructionId != -1){
				preparedStatement.setInt(1, purchaseInstructionId);
			}
			productDAO = new ProductDAO();
			
			rs = executeQuery(preparedStatement);
			while (rs.next()){
				dto  = new InstructionPurchaseDetailDTO();
				dto.setPurchaseInstructionDetailid(rs.getInt(1));
				dto.setPurchaseInstructionId(rs.getInt(2));
				dto.setRow(rs.getInt(3));
				dto.setCustomerId(rs.getString(4));
				dto.setCustomerDescription(rs.getString(5));
				dto.setProductViews(productDAO.getPurchaseIntructionItemDetails(dto.getPurchaseInstructionDetailid()));
				dto.setItemQuantity(rs.getDouble(6));
				dto.setCornQuantity(rs.getDouble(7));
				dto.setFutureTypeId(rs.getString(8));
				dto.setInstructionStrategyId(rs.getInt(9));
				dto.setInstructionTypeId(rs.getInt(10));
				dto.setFutures(rs.getInt(11));
				dto.setTons(rs.getDouble(12));
				dto.setStatus(rs.getInt(13));
				listOfPurchaseInstructionDetails.add(dto);
			}	
		}catch(ProductException productException){
			InstructionsForecastsException instructionsForecastsException = null;
			productException.printStackTrace(System.out);
			instructionsForecastsException = new InstructionsForecastsException(productException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionsForecastsException;
		}catch (PoolException poolException){
			InstructionsForecastsException instructionsForecastsException = null;
			poolException.printStackTrace(System.out);
			instructionsForecastsException = new InstructionsForecastsException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionsForecastsException;	
		} catch (SQLException sqlException) {
			InstructionsForecastsException instructionsForecastsException = null;
			sqlException.printStackTrace(System.out);
			instructionsForecastsException = new InstructionsForecastsException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw instructionsForecastsException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfPurchaseInstructionDetails;		
	}
	
	/**
	 * Obtiene un {@link InstructionPurchaseDetailDTO} basado en su ID
	 * @param purchaseInstructionDetailId
	 * @return
	 * @throws InstructionsForecastsException
	 */
	public InstructionPurchaseDetailDTO getById(int purchaseInstructionDetailId)throws InstructionPurchaseDetailException, ProductException{		
		PreparedStatement preparedStatement = null;
		InstructionPurchaseDetailDTO dto = null;		
		ResultSet rs = null;
		ProductDAO productDAO = null;
		try{
			productDAO = new ProductDAO();
			preparedStatement = prepareSQL(SELECT_PURCHASE_INSTRUCTION_DETAIL, DATASOURCE);			
			preparedStatement.setInt(1, purchaseInstructionDetailId);
			rs = executeQuery(preparedStatement);
			if(rs.next()){
				dto  = new InstructionPurchaseDetailDTO();
				dto.setPurchaseInstructionDetailid(rs.getInt(1));
				dto.setPurchaseInstructionId(rs.getInt(2));
				dto.setRow(rs.getInt(3));
				dto.setCustomerId(rs.getString(4));
				dto.setCustomerDescription(rs.getString(5));
				dto.setProductViews(productDAO.getPurchaseIntructionItemDetails(dto.getPurchaseInstructionDetailid()));
				dto.setItemQuantity(rs.getDouble(6));
				dto.setCornQuantity(rs.getDouble(7));
				dto.setFutureTypeId(rs.getString(8));
				dto.setInstructionStrategyId(rs.getInt(9));
				dto.setInstructionTypeId(rs.getInt(10));
				dto.setFutures(rs.getInt(11));
				dto.setTons(rs.getDouble(12));
				dto.setStatus(rs.getInt(13));				
			}
		}catch (PoolException poolException){
			InstructionPurchaseDetailException instructionPurchaseDetailException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseDetailException = new InstructionPurchaseDetailException(poolException, InstructionPurchaseDetailException.LAYER_DAO,InstructionPurchaseDetailException.ACTION_LISTS);
			throw instructionPurchaseDetailException;	
		} catch (SQLException sqlException) {
			InstructionPurchaseDetailException instructionPurchaseDetailException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseDetailException = new InstructionPurchaseDetailException(sqlException, InstructionPurchaseDetailException.LAYER_DAO, InstructionPurchaseDetailException.ACTION_LISTS);
			throw instructionPurchaseDetailException;
		} catch (ProductException productException) {
			throw productException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return dto;
	}

	public void changeStatusInstructionsDetail(int purchaseInstructionId, int canceledStatusId ) throws InstructionPurchaseDetailException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_CHANGE_STATUS_DETAIL, dsName);
			preparedStatement.setInt(1, canceledStatusId);
			preparedStatement.setInt(2, purchaseInstructionId);
			executeUpdate(preparedStatement);
		}catch (PoolException poolException){
			InstructionPurchaseDetailException instructionPurchaseDetailException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseDetailException = new InstructionPurchaseDetailException(poolException, InstructionPurchaseDetailException.LAYER_DAO,InstructionPurchaseDetailException.ACTION_LISTS);
			throw instructionPurchaseDetailException;	
		} catch (SQLException sqlException) {
			InstructionPurchaseDetailException instructionPurchaseDetailException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseDetailException = new InstructionPurchaseDetailException(sqlException, InstructionPurchaseDetailException.LAYER_DAO, InstructionPurchaseDetailException.ACTION_LISTS);
			throw instructionPurchaseDetailException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	
	public void changeStatusInstructionsDetailByDetailID(int purchaseInstructionDetailId, int canceledStatusId ) throws InstructionPurchaseDetailException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_CHANGE_STATUS_DETAIL_BY_ID, dsName);
			preparedStatement.setInt(1, canceledStatusId);
			preparedStatement.setInt(2, purchaseInstructionDetailId);
			executeUpdate(preparedStatement);
		}catch (PoolException poolException){
			InstructionPurchaseDetailException instructionPurchaseDetailException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseDetailException = new InstructionPurchaseDetailException(poolException, InstructionPurchaseDetailException.LAYER_DAO,InstructionPurchaseDetailException.ACTION_LISTS);
			throw instructionPurchaseDetailException;	
		} catch (SQLException sqlException) {
			InstructionPurchaseDetailException instructionPurchaseDetailException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseDetailException = new InstructionPurchaseDetailException(sqlException, InstructionPurchaseDetailException.LAYER_DAO, InstructionPurchaseDetailException.ACTION_LISTS);
			throw instructionPurchaseDetailException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	/**
	 * Actualiza un {@link InstructionPurchaseDetailDTO
	 * @param instructionPurchaseDetailDTO
	 * @throws InstructionPurchaseException
	 */
	public void update(InstructionPurchaseDetailDTO instructionPurchaseDetailDTO) throws InstructionPurchaseDetailException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_UPDATE_DETAIL, dsName);
			preparedStatement.setString(1, instructionPurchaseDetailDTO.getFutureTypeId());
			preparedStatement.setInt(2, instructionPurchaseDetailDTO.getInstructionStrategyId());
			preparedStatement.setInt(3, instructionPurchaseDetailDTO.getInstructionTypeId());
			preparedStatement.setDouble(4, instructionPurchaseDetailDTO.getItemQuantity());
			preparedStatement.setDouble(5, instructionPurchaseDetailDTO.getCornQuantity());
			preparedStatement.setInt(6, instructionPurchaseDetailDTO.getStatus());
			preparedStatement.setInt(7, instructionPurchaseDetailDTO.getPurchaseInstructionDetailid());
			executeUpdate(preparedStatement);
		}catch (PoolException poolException){
			InstructionPurchaseDetailException instructionPurchaseDetailException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseDetailException = new InstructionPurchaseDetailException(poolException, InstructionPurchaseDetailException.LAYER_DAO,InstructionPurchaseDetailException.ACTION_LISTS);
			throw instructionPurchaseDetailException;	
		} catch (SQLException sqlException) {
			InstructionPurchaseDetailException instructionPurchaseDetailException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseDetailException = new InstructionPurchaseDetailException(sqlException, InstructionPurchaseDetailException.LAYER_DAO, InstructionPurchaseDetailException.ACTION_LISTS);
			throw instructionPurchaseDetailException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	
	/**
	 * Inserta un {@link InstructionPurchaseDetailDTO}
	 * @param instructionPurchaseDetailDTO
	 * @throws InstructionPurchaseException
	 */
	public void insert(InstructionPurchaseDetailDTO instructionPurchaseDetailDTO) throws InstructionPurchaseException{
		PreparedStatement preparedStatement = null;
		String id = "-1";
		try{
			preparedStatement = prepareSQL(SQL_INSERT_DETAIL, dsName);
			preparedStatement.setInt(1, instructionPurchaseDetailDTO.getPurchaseInstructionId());
			preparedStatement.setInt(2, instructionPurchaseDetailDTO.getPurchaseInstructionId());
			preparedStatement.setString(3, instructionPurchaseDetailDTO.getCustomerId());
			preparedStatement.setDouble(4, instructionPurchaseDetailDTO.getItemQuantity());
			preparedStatement.setDouble(5, instructionPurchaseDetailDTO.getCornQuantity());
			preparedStatement.setString(6, instructionPurchaseDetailDTO.getFutureTypeId());
			preparedStatement.setInt(7, instructionPurchaseDetailDTO.getInstructionStrategyId());
			preparedStatement.setInt(8, instructionPurchaseDetailDTO.getInstructionTypeId());
			preparedStatement.setInt(9, instructionPurchaseDetailDTO.getStatus());
			executeUpdate(preparedStatement);
			id = this.getInsertedId(preparedStatement.getConnection());
			instructionPurchaseDetailDTO.setPurchaseInstructionDetailid(Integer.parseInt(id));
		}catch (PoolException poolException){
			InstructionPurchaseException instructionPurchaseException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;	
		} catch (SQLException sqlException) {
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	
	public void insertItemDetail(int purchaseInstructionDetailId, String productId)throws InstructionPurchaseException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_INSERT_PRODUCT_DETAIL, dsName);
			preparedStatement.setInt(1, purchaseInstructionDetailId);
			preparedStatement.setString(2, productId);
			executeUpdate(preparedStatement);
		}catch (PoolException poolException){
			InstructionPurchaseException instructionPurchaseException = null;
			poolException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;	
		} catch (SQLException sqlException) {
			InstructionPurchaseException instructionPurchaseException = null;
			sqlException.printStackTrace(System.out);
			instructionPurchaseException = new InstructionPurchaseException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw instructionPurchaseException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
}